#!/usr/local/bin/tclsh8.5
package require Oratcl
#EDITABLE OPTIONS##################################################
set total_iterations 10000 ;# Number of transactions before logging off
set RAISEERROR "false" ;# Exit script on Oracle error (true or false)
set KEYANDTHINK "false" ;# Time for user thinking and keying (true or false)
set connect tpcc/tpcc@oracle ;# Oracle connect string for tpc-c user
#EDITABLE OPTIONS##################################################
#RANDOM NUMBER
proc RandomNumber { min max } {
    set maxFactor [expr {[expr {$max + 1}] - $min}]
    set value [expr {int([expr {rand() * 99999999}])}]
    set value [expr {[expr {$value % $maxFactor}] + $min}]
return $value
}
#STANDARD SQL
proc standsql { curn sql } {
set count 0
if {[catch {orasql $curn $sql} message]} {
error "SQL statement failed: $sql : $message"
} else {
orafetch  $curn -datavariable output
while { [ oramsg  $curn ] == 0 } {
lappend ftch $output
orafetch  $curn -datavariable output
incr count
}
return $ftch
}
}
#NURand function
proc NURand { iConst x y C } {
set ran1 [ expr {[ RandomNumber 0 $iConst ]} ]
set ran2 [ expr {[ RandomNumber $x $y ]} ]
set rand_num [ expr {[expr {[ expr {( $ran1 | $ran2 )}]} + $C % [ expr {$y - $x + 1}] + $x ]}]
return $rand_num
}
#RANDOM NAME
proc randname { num } {
array set namearr { 0 BAR 1 OUGHT 2 ABLE 3 PRI 4 PRES 5 ESE 6 ANTI 7 
CALLY 8 ATION 9 EING }
set name [ concat $namearr([ expr {( $num / 100 ) % 10 }])$namearr([ expr {( $num / 10 ) % 10 }])$namearr([ expr {( $num / 1 ) % 10 }]) ]
return $name
}
#TIMESTAMP
proc gettimestamp { } {
set tstamp [ clock format [ clock seconds ] -format %Y%m%d%H%M%S ]
return $tstamp
}
#KEYING TIME
proc keytime { keying } {
after [ expr {$keying * 1000} ]
return
}
#THINK TIME
proc thinktime { thinking } {
set thinkingtime [ expr {abs(round(log(rand()) * $thinking))} ]
after [ expr {$thinkingtime * 1000} ]
return
}
#NEW ORDER
proc neword { lda no_w_id w_id_input RAISEERROR } {
#open new order cursor
#2.4.1.2 select district id randomly from home warehouse where d_w_id = d_id
set no_d_id [ RandomNumber 1 10 ]
#2.4.1.2 Customer id randomly selected where c_d_id = d_id and c_w_id = w_id
set no_c_id [ RandomNumber 1 3000 ]
#2.4.1.3 Items in the order randomly selected from 5 to 15
set ol_cnt [ RandomNumber 5 15 ]
#2.4.1.6 order entry date O_ENTRY_D generated by SUT
set curn1 [oraopen $lda ]
set date [ gettimestamp ]
set sql5 "begin 
neword(:no_w_id,:no_max_w_id,:no_d_id,:no_c_id,:no_o_ol_cnt,:no_c_discount,:no_c_last,:no_c_credit,:no_d_tax,:no_w_tax,:no_d_next_o_id,TO_DATE(:timestamp,'YYYYMMDDHH24MISS')); 
END;"
oraparse $curn1 $sql5
orabind $curn1 :no_w_id $no_w_id :no_max_w_id $w_id_input :no_d_id $no_d_id :no_c_id $no_c_id :no_o_ol_cnt $ol_cnt :no_c_discount {} :no_c_last {} :no_c_credit {} :no_d_tax {} :no_w_tax {} :no_d_next_o_id {0} :timestamp $date
if {[catch {oraexec $curn1} message]} {
if { $RAISEERROR } {
error "New Order : $message [ oramsg $curn1 all ]"
	} else {
puts $message
	} } else {
orafetch  $curn1 -datavariable output
puts $output
	}
oraclose $curn1
}
#PAYMENT
proc payment { lda p_w_id w_id_input RAISEERROR } {
#2.5.1.1 The home warehouse id remains the same for each terminal
set curn2 [oraopen $lda ]
#2.5.1.1 select district id randomly from home warehouse where d_w_id = d_id
set p_d_id [ RandomNumber 1 10 ]
#2.5.1.2 customer selected 60% of time by name and 40% of time by number
set x [ RandomNumber 1 100 ]
set y [ RandomNumber 1 100 ]
if { $x <= 85 } {
set p_c_d_id $p_d_id
set p_c_w_id $p_w_id
} else {
#use a remote warehouse
set p_c_d_id [ RandomNumber 1 10 ]
set p_c_w_id [ RandomNumber 1 $w_id_input ]
while { ($p_c_w_id == $p_w_id) && ($w_id_input != 1) } {
set p_c_w_id [ RandomNumber 1  $w_id_input ]
	}
}
set nrnd [ NURand 255 0 999 123 ]
set name [ randname $nrnd ]
set p_c_id [ RandomNumber 1 3000 ]
if { $y <= 60 } {
#use customer name
#C_LAST is generated
set byname 1
 } else {
#use customer number
set byname 0
set name {}
 }
#2.5.1.3 random amount from 1 to 5000
set p_h_amount [ RandomNumber 1 5000 ]
#2.5.1.4 date selected from SUT
set h_date [ gettimestamp ]
#2.5.2.1 Payment Transaction
#change following to correct values
set sql5 "BEGIN 
payment(:p_w_id,:p_d_id,:p_c_w_id,:p_c_d_id,:p_c_id,:byname,:p_h_amount,:p_c_last,:p_w_street_1,:p_w_street_2,:p_w_city,:p_w_state,:p_w_zip,:p_d_street_1,:p_d_street_2,:p_d_city,:p_d_state,:p_d_zip,:p_c_first,:p_c_middle,:p_c_street_1,:p_c_street_2,:p_c_city,:p_c_state,:p_c_zip,:p_c_phone,:p_c_since,:p_c_credit,:p_c_credit_lim,:p_c_discount,:p_c_balance,:p_c_data,TO_DATE(:timestamp,'YYYYMMDDHH24MISS')); 
END;"
oraparse $curn2 $sql5
orabind $curn2 :p_w_id $p_w_id :p_d_id $p_d_id :p_c_w_id $p_c_w_id :p_c_d_id $p_c_d_id :p_c_id $p_c_id :byname $byname :p_h_amount $p_h_amount :p_c_last $name :p_w_street_1 {} :p_w_street_2 {} :p_w_city {} :p_w_state {} :p_w_zip {} :p_d_street_1 {} :p_d_street_2 {} :p_d_city {} :p_d_state {} :p_d_zip {} :p_c_first {} :p_c_middle {} :p_c_street_1 {} :p_c_street_2 {} :p_c_city {} :p_c_state {} :p_c_zip {} :p_c_phone {} :p_c_since {} :p_c_credit {0} :p_c_credit_lim {} :p_c_discount {} :p_c_balance {0} :p_c_data {} :timestamp $h_date
if {[ catch {oraexec $curn2} message]} {
if { $RAISEERROR } {
error "Payment : $message [ oramsg $curn2 all ]"
	} else {
puts $message
} } else {
orafetch  $curn2 -datavariable output
puts $output
}
oraclose $curn2
}
#ORDER_STATUS
proc ostat { lda w_id RAISEERROR } {
set curn3 [oraopen $lda ]
#2.5.1.1 select district id randomly from home warehouse where d_w_id = d_id
set d_id [ RandomNumber 1 10 ]
set nrnd [ NURand 255 0 999 123 ]
set name [ randname $nrnd ]
set c_id [ RandomNumber 1 3000 ]
set y [ RandomNumber 1 100 ]
if { $y <= 60 } {
set byname 1
 } else {
set byname 0
set name {}
}
set sql2 "BEGIN ostat(:os_w_id,:os_d_id,:os_c_id,:byname,:os_c_last,:os_c_first,:os_c_middle,:os_c_balance,:os_o_id,:os_entdate,:os_o_carrier_id); END;"
oraparse $curn3 $sql2
orabind $curn3 :os_w_id $w_id :os_d_id $d_id :os_c_id $c_id :byname $byname :os_c_last $name :os_c_first {} :os_c_middle {} :os_c_balance {0} :os_o_id {} :os_entdate {} :os_o_carrier_id {} 
if {[catch {oraexec $curn3} message]} {
if { $RAISEERROR } {
error "Order Status : $message [ oramsg $curn3 all ]"
	} else {
puts $message
} } else {
orafetch  $curn3 -datavariable output
puts $output
}
oraclose $curn3
}
#DELIVERY
proc delivery { lda w_id RAISEERROR } {
set curn4 [oraopen $lda ]
set carrier_id [ RandomNumber 1 10 ]
set date [ gettimestamp ]
set sql2 "BEGIN delivery(:d_w_id,:d_o_carrier_id,TO_DATE(:timestamp,'YYYYMMDDHH24MISS')); END;"
oraparse $curn4 $sql2
orabind $curn4 :d_w_id $w_id :d_o_carrier_id $carrier_id :timestamp $date
if {[ catch {oraexec $curn4} message ]} {
if { $RAISEERROR } {
error "Delivery : $message [ oramsg $curn4 all ]"
	} else {
puts $message
} } else {
orafetch  $curn4 -datavariable output
puts $output
}
oraclose $curn4
}
#STOCK LEVEL
proc slev { lda w_id stock_level_d_id RAISEERROR } {
set curn5 [oraopen $lda ]
set threshold [ RandomNumber 10 20 ]
set sql1 "BEGIN slev(:st_w_id,:st_d_id,:threshold); END;"
oraparse $curn5 $sql1
orabind $curn5 :st_w_id $w_id :st_d_id $stock_level_d_id :THRESHOLD $threshold 
if {[catch {oraexec $curn5 $sql1} message]} { 
if { $RAISEERROR } {
error "Stock Level : $message [ oramsg $curn5 all ]"
	} else {
puts $message
} } else {
orafetch  $curn5 -datavariable output
puts $output
}
oraclose $curn5
}
#RUN TPC-C
set lda [oralogon $connect]
oraautocom $lda on
set curn1 [oraopen $lda ]
set sql1 "select max(w_id) from warehouse"
set w_id_input [ standsql $curn1 $sql1 ]
#2.4.1.1 set warehouse_id stays constant for a given terminal
set w_id  [ RandomNumber 1 $w_id_input ]  
set sql2 "select max(d_id) from district"
set d_id_input [ standsql $curn1 $sql2 ]
set stock_level_d_id  [ RandomNumber 1 $d_id_input ]  
set sql3 "BEGIN DBMS_RANDOM.initialize (val => TO_NUMBER(TO_CHAR(SYSDATE,'MMSS')) * (USERENV('SESSIONID') - TRUNC(USERENV('SESSIONID'),-5))); END;"
oraparse $curn1 $sql3
if {[catch {oraplexec $curn1 $sql3} message]} {
error "Failed to initialise DBMS_RANDOM $message have you run catoctk.sql as sys?" }
oraclose $curn1
for {set it 0} {$it < $total_iterations} {incr it} {
set choice [ RandomNumber 1 23 ]
if {$choice <= 10} {
puts "new order"
if { $KEYANDTHINK } { keytime 18 }
neword $lda $w_id $w_id_input $RAISEERROR
if { $KEYANDTHINK } { thinktime 12 }
} elseif {$choice <= 20} {
puts "payment"
if { $KEYANDTHINK } { keytime 3 }
payment $lda $w_id $w_id_input $RAISEERROR
if { $KEYANDTHINK } { thinktime 12 }
} elseif {$choice <= 21} {
puts "delivery"
if { $KEYANDTHINK } { keytime 2 }
delivery $lda $w_id $RAISEERROR
if { $KEYANDTHINK } { thinktime 10 }
} elseif {$choice <= 22} {
puts "stock level"
if { $KEYANDTHINK } { keytime 2 }
slev $lda $w_id $stock_level_d_id $RAISEERROR
if { $KEYANDTHINK } { thinktime 5 }
} elseif {$choice <= 23} {
puts "order status"
if { $KEYANDTHINK } { keytime 2 }
ostat $lda $w_id $RAISEERROR
if { $KEYANDTHINK } { thinktime 5 }
	}
}
oralogoff $lda

